package com.pointi.emoClientM ;

import java.sql.ResultSet ;
import java.sql.SQLException ;
import java.sql.Statement ;
import java.util.ArrayList ;

import com.pointi.emoTools.WorkList ;
import com.pointi.emoTools.emoJDBC ;

public class JDBC extends emoJDBC
{
	// ____________________________________________________________________________________________________
	// t_callkey_checklist_yyyymm

	public void fnUpdateCheckList(String inColumn, int inValue, int inCallKey)
	{
		String query = "" ;
		query = " UPDATE t_callkey_checklist_" + fnGetYYYYMM() + " " +
				" SET " + inColumn + " = '" + inValue + "' " +
				" WHERE callkey = " + inCallKey ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}
	
	public boolean fnIsWorkAvailable(String inCallKey)
	{
		boolean mOUT = false ;
		Statement mST = null ;
		ResultSet mRS = null ;
		String query = "" ;
		query = " SELECT callkey " +
				" FROM t_callkey_checklist_" + fnGetYYYYMM() + " " +
				" WHERE callkey = " + inCallKey + " AND check_converter = 3 AND check_client = 0" ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				mOUT = true ;
				break ;
			}
		}
		catch (SQLException se) { System. out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return mOUT ;
	}
	
	// ____________________________________________________________________________________________________
	// t_callkey_recfile_yyyymm

	public void fnGetFileInfo(WorkList inWork)
	{
		Statement mST = null ;
		ResultSet mRS = null ;
		String mTargetPath = "" ;
		String mTargetName = "" ;
		String query = "" ;
		query = " SELECT file_path_tgt, file_name_tgt " +
				" FROM t_callkey_recfile_" + this.fnGetYYYYMM() + " " +
				" WHERE callkey = " + inWork.fnGetCallKey() ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				mTargetPath = mRS.getString("file_path_tgt") ;
				mTargetName = mRS.getString("file_name_tgt") ;
			}
			inWork.fnSetFileInfo(mTargetPath, mTargetName) ;
		}
		catch (SQLException se) { System. out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}

	// ____________________________________________________________________________________________________
	// t_info_server

	public void fnGetServerInfo(WorkList inWork)
	{
		Statement mST = null ;
		ResultSet mRS = null ;
		String mAddress = "" ;
		String mPort = "" ;
		String query = "" ;
		query = " SELECT server_ip, server_port " +
				" FROM t_info_server " +
				" WHERE server_no = " + inWork.fnGetServerNo() ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				mAddress = mRS.getString("server_ip") ;
				mPort = mRS.getString("server_port") ;
			}
			inWork.fnSetDestination(mAddress, mPort) ;
		}
		catch (SQLException se) { System. out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}
	
	public void fnGetServerStatus(ArrayList<String> mAliveServer)
	{
		Statement mST = null ;
		ResultSet mRS = null ;
		String query = "" ;
		query = " SELECT server_no " +
				" FROM t_info_server " + 
				" WHERE server_alive = 1" ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				String mServerNo = String.valueOf(mRS.getInt("server_no")) ;
				if (!mAliveServer.contains(mServerNo)) mAliveServer.add(mServerNo) ;
			}
		}
		catch (SQLException se) { System. out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}
	
	public boolean fnIsServerAlive(String inServerNo)
	{
		boolean mOUT = false ;
		Statement mST = null ;
		ResultSet mRS = null ;
		String query = "" ;
		query = " SELECT server_alive " +
				" FROM t_info_server " +
				" WHERE server_no = " + inServerNo + " " ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				if (mRS.getInt("server_alive") == 1) {
					mOUT = true ;
					break ;
				}
			}
		}
		catch (SQLException se) { System. out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return mOUT ;
	}

	public void fnReassignServer(String inSeq, String inClientNo)
	{
		int _NONE = 9999 ;
		int mClientNo = Integer.valueOf(inClientNo) ;
		int mServerNo = 0 ;
		int candidateA = _NONE ;
		int candidateB = _NONE ;
		Statement mST = null ;
		ResultSet mRS = null ;
		String query = "" ;
		query = " SELECT server_no " +
				" FROM t_info_server " +
				" WHERE server_alive = 1 " ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ; // Get Alive Server List
			while (mRS.next()) {
				int tServerNo = mRS.getInt("server_no") ;
				if (candidateA == _NONE) { candidateA = tServerNo ; }
				else if (tServerNo > mClientNo && candidateB == _NONE) { candidateB = tServerNo ; break ; }
			}
		}
		catch (SQLException se) { System. out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		if (candidateB != _NONE) { mServerNo = candidateB ; }
		else { mServerNo = candidateA ; }
		query = " UPDATE t_gw_work_scheduler " +
				" SET server_no = " + String.valueOf(mServerNo) + " " +
				" WHERE seq = " + inSeq + " ; " ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}

	// ____________________________________________________________________________________________________
	// t_info_client

	public void fnGetClientStatus(ArrayList<String> mAliveClient)
	{
		Statement mST = null ;
		ResultSet mRS = null ;
		String query = "" ;
		query = " SELECT client_no " +
				" FROM t_info_client " + 
				" WHERE client_alive = 1" ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				String mClientNo = String.valueOf(mRS.getInt("client_no")) ;
				if (!mAliveClient.contains(mClientNo)) mAliveClient.add(mClientNo) ;
			}
		}
		catch (SQLException se) { System. out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}
	
	public void fnUpdateClientStatus(CheckStatus inStatus)
	{
		String query = "" ;
		query = " UPDATE t_info_client " +
				" SET client_status = " + inStatus.fnGetClientStatus() + ", client_count = " + inStatus.fnGetClientCount() + " " +
				" WHERE client_no = " + inStatus.fnGetClientNo() + " ; " ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}

	// ____________________________________________________________________________________________________
	// t_gw_work_scheduler

	public void fnGetWorkScheduleForClient(String inClientNo, ArrayList<WorkList> inWorkList)
	{
		Statement mST = null ;
		ResultSet mRS = null ;
		String query = "" ;
		// TODO // TOP 100 Option is must Match with License Key
		query = " SELECT TOP 100 seq, callkey, client_no, server_no " +
				" FROM t_gw_work_scheduler " +
				" WHERE client_no = " + inClientNo + " " ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				String mSeq = String.valueOf(mRS.getInt("seq")) ;
				String mCallKey = String.valueOf(mRS.getInt("callkey")) ;
				String mClientNo = String.valueOf(mRS.getInt("client_no")) ;
				String mServerNo = String.valueOf(mRS.getInt("server_no")) ;
				WorkList mWorkList = new WorkList(mSeq, mCallKey, mClientNo, mServerNo) ; 
				if (!inWorkList.contains(mWorkList)) inWorkList.add(mWorkList) ;
			}
		}
		catch (SQLException se) { System. out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}
	
	public void fnInsertWorkSchedule(String inCallKey, String inClient, String inServer)
	{
		String query = "" ;
		query = " INSERT INTO t_gw_work_scheduler (callkey, client_no, server_no, regdate) " + 
				" VALUES ( " + inCallKey + ", " + inClient + ", " + inServer + ", '" + fnGetRegDate() + "' )" ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}

	public void fnDeleteWorkFromScheduler(String inSeq)
	{
		String query = "" ;
		query = " DELETE FROM t_gw_work_scheduler " + 
				" WHERE seq = " + inSeq + " ; " ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}
	public void fnDeleteWorkFromScheduler(int inSeq) { this.fnDeleteWorkFromScheduler(String.valueOf(inSeq)) ; }

	// ____________________________________________________________________________________________________
}
